** Exploring LAUS County Data
** Get county level unemployment statistics
** source: http://download.bls.gov/pub/time.series/la/
** JHL

*************************************
** Set up workspace
*************************************
version 14.0
clear all
set more off
cd "${path_home}"
adopath + ../programs
** log using "${path_log}/c03_laus_counties", text replace

*************************************
** Start work here
*************************************
timer on 1

*************************************
** [1] Convert mapping files to dta
*************************************
import delimited "${path_big_dta}/laus/la.area.txt", varname(1) clear
keep if area_type == "F"
drop area_ty d sel sort v
split area_text, p(", ")
rename area_text1 countyname
rename area_text2 state
replace countyname = "Anchorage Municipality" if countyname == "Anchorage Borough/municipality"
replace countyname = "Broomfield County" if countyname == "Broomfield County/city"
replace countyname = "Denver County" if countyname == "Denver County/city"
replace countyname = "Honolulu County" if countyname == "Honolulu County/city"
replace state = "DC" if countyname == "District of Columbia"
replace countyname = "Juneau City and Borough" if countyname == "Juneau Borough/city"
replace countyname = "La Salle Parish" if countyname == "LaSalle Parish"
replace countyname = "Nantucket County" if countyname == "Nantucket County/town"
replace countyname = "Petersburg Census Area" if countyname == "Petersburg Borough"
replace countyname = "Philadelphia County" if countyname == "Philadelphia County/city"
replace countyname = "San Francisco County" if countyname == "San Francisco County/city"
replace countyname = "Sitka City and Borough" if countyname == "Sitka Borough/city"
replace countyname = "Wrangell City and Borough" if countyname == "Wrangell Borough/city"
replace countyname = "Yakutat City and Borough" if countyname == "Yakutat Borough/city"
replace countyname = "Wade Hampton Census Area" if countyname == "Kusilvak Census Area"
replace countyname = "Shannon County" if countyname == "Oglala Lakota County"
merge 1:1 state countyname using "${path_big_dta}/us_counties/national_county.dta", keep( match ) nogen
rename countyfp fips_county_code
rename statefp fips_state_code
save "${path_big_dta}/laus/laus_county_codes", replace

*************************************
** [2] Import county data, clean, reshape, merge
*************************************
import delimited "${path_big_dta}/laus/la.data.64.County", varnames(1) clear
keep if year > 2003

*** Get area and measure codes
local length = length( series_id )
gen area_code = substr( series_id, 4, 15 )
gen measure_code = real( substr( series_id, 19, 20 ) )

** recode month
gen month = real( substr( period, 2, 2 ) )

** reshape
drop period series_id footnote
reshape wide value, i( year month area_code ) j( measure_code )

** merge county identifiers
merge m:1 area_code using "${path_big_dta}/laus/laus_county_codes", keep( match ) nogen

** rename values
foreach v of var value* {
	replace `v' = "" if `v' == "           -"
}
destring value*, replace
rename value3 unemployment_rate_county
lab var unemployment_rate_c "unemployment rate (county)"
rename value4 unemployment_county
lab var unemployment_c "unemployment (county)"
rename value5 la_employment_county
lab var la_employment_c "laus employment (county)"
rename value6 labor_force_county
lab var labor_force_c "labor force (county)"

drop area_code area_text countyname state classfp

save "${path_big_dta}/laus/laus_counties", replace 

** Quarterly
use "${path_big_dta}/laus/laus_counties", clear		
gen quarter = ceil(month/3)		
collapse unemployment_rate_county unemployment_county la_employment_county labor_force_county, by(fips_state_code fips_county_code year quarter)
save "${path_big_dta}/laus/laus_counties_q", replace

*************************************
** Close workspace
*************************************
** log close
timer off 1
timer list 1